package com.oriental.manage.core.excelUtils;

import com.oriental.manage.core.exception.BusiException;
import com.oriental.manage.core.fileUtils.TemplateExcelWriterCallBack;
import com.oriental.manage.pojo.report.DayAccountReportData;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.webbuilder.office.excel.api.poi.callback.POIExcelWriterProcessor;
import org.webbuilder.office.excel.config.ExcelWriterConfig;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PushbackInputStream;
import java.util.List;

/**
 * Created by lupf on 2016/7/21.
 */
@Slf4j
public class ExcelTemplateUtil {

    public static void write(InputStream inputStream, OutputStream outputStream, ExcelWriterConfig config) throws IOException, InvalidFormatException {

        if (!inputStream.markSupported()) {
            inputStream = new PushbackInputStream(inputStream, 8);
        }

        Workbook wb;
        if (POIFSFileSystem.hasPOIFSHeader(inputStream)) {
            try {
                wb = new HSSFWorkbook(inputStream);
            } catch (Exception e) {
                log.error("系统异常", e);
                wb = new XSSFWorkbook(inputStream);
            }
        } else if (POIXMLDocument.hasOOXMLHeader(inputStream)) {
            try {
                wb = WorkbookFactory.create(inputStream);
            } catch (InvalidFormatException e) {
                log.error("系统异常", e);
                wb = new XSSFWorkbook(inputStream);
            }
        } else {
            throw new BusiException("不支持的EXCEL类型");
        }

        POIExcelWriterProcessor processor = new POIExcelWriterProcessor(outputStream, wb);

        TemplateExcelWriterCallBack callBack = new TemplateExcelWriterCallBack(config);

        callBack.getConfig().setSheetName(wb.getSheetName(0));
        callBack.render(processor);
        outputStream.flush();

    }

    //对标题添加时间
    public static void write(InputStream inputStream, OutputStream outputStream, ExcelWriterConfig config, String time) throws IOException, InvalidFormatException {

        if (!inputStream.markSupported()) {
            inputStream = new PushbackInputStream(inputStream, 8);
        }

        Workbook wb;
        if (POIFSFileSystem.hasPOIFSHeader(inputStream)) {
            try {
                wb = new HSSFWorkbook(inputStream);
                CellStyle style = wb.createCellStyle();
                Sheet sheetAt = wb.getSheetAt(0);
                Row row = sheetAt.getRow(1);
                Cell cell = row.getCell(0);
                cell.setCellStyle(style);
                cell.setCellValue("日期范围：" + time);
            } catch (Exception e) {
                log.error("系统异常", e);
                wb = new XSSFWorkbook(inputStream);
            }
        } else if (POIXMLDocument.hasOOXMLHeader(inputStream)) {
            try {
                wb = new XSSFWorkbook(inputStream);
                CellStyle style = wb.createCellStyle();
                style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);//右侧
                Sheet sheetAt = wb.getSheetAt(0);
                Row row = sheetAt.getRow(1);
                Cell cell = row.getCell(0);
                cell.setCellStyle(style);
                cell.setCellValue("日期范围：" + time);
            } catch (Exception e) {
                log.error("系统异常", e);
                wb = new XSSFWorkbook(inputStream);
            }
        } else {
            throw new BusiException("不支持的EXCEL类型");
        }

        POIExcelWriterProcessor processor = new POIExcelWriterProcessor(outputStream, wb);

        TemplateExcelWriterCallBack callBack = new TemplateExcelWriterCallBack(config);

        callBack.getConfig().setSheetName(wb.getSheetName(0));
        callBack.render(processor);
        outputStream.flush();

    }

    //对标题添加填报时间 edit by huzhen
    public static void write(InputStream inputStream, OutputStream outputStream, ExcelWriterConfig config,
                             String time, String flag, List<DayAccountReportData> dayAccountReportDataList) throws IOException, InvalidFormatException {

        if (!inputStream.markSupported()) {
            inputStream = new PushbackInputStream(inputStream, 8);
        }

        Workbook wb;
        if (POIFSFileSystem.hasPOIFSHeader(inputStream)) {
            try {
                wb = new HSSFWorkbook(inputStream);
                CellStyle style = wb.createCellStyle();
                Sheet sheetAt = wb.getSheetAt(0);
                Row row = sheetAt.getRow(1);
                Cell cell = row.getCell(0);
                cell.setCellStyle(style);
                cell.setCellValue("填报日期：" + time);
            } catch (Exception e) {
                log.error("系统异常", e);
                wb = new XSSFWorkbook(inputStream);
            }
        } else if (POIXMLDocument.hasOOXMLHeader(inputStream)) {
            try {
                wb = new XSSFWorkbook(inputStream);
                CellStyle style = wb.createCellStyle();
                style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//中间
                Sheet sheetAt = wb.getSheetAt(0);
                Row row = sheetAt.getRow(2);
                Cell cell = row.getCell(0);
                cell.setCellStyle(style);
                cell.setCellValue("填报日期：" + time);

                if(flag.equals("0")) {
                    Row row1 = sheetAt.createRow((short) (sheetAt.getLastRowNum() + config.getDatas().size() + 2));
                    row1.createCell((short) 1).setCellValue("填表说明:");
                    Row row2 = sheetAt.createRow((short) (sheetAt.getLastRowNum() + 1));
                    row2.createCell((short) 1).setCellValue("1、填表日期为当日处理日期T+1日。");
                    Row row3 = sheetAt.createRow((short) (sheetAt.getLastRowNum() + 1));
                    row3.createCell((short) 1).setCellValue("2、支付机构侧日结数据为T日实际处理数据（节假日收到节后处理数据应按处理日归计）。");
                    Row row4 = sheetAt.createRow((short) (sheetAt.getLastRowNum() + 1));
                    row4.createCell((short) 1).setCellValue("3、此表平衡关系：1=2=3（1-2+3-4）。");
                    Row row5 = sheetAt.createRow((short) (sheetAt.getLastRowNum() + 1));
                    row5.createCell((short) 1).setCellValue("4、此表与备付金日结平衡表对应关系：1=1；2=2+3（1-2）；3=4。");
                } else if (flag.equals("1")) {
                    Row row1 = sheetAt.createRow((short) (sheetAt.getLastRowNum() + config.getDatas().size() + 2));
                    row1.createCell((short) 1).setCellValue("填表说明:");
                    Row row2 = sheetAt.createRow((short) (sheetAt.getLastRowNum() + 1));
                    row2.createCell((short) 1).setCellValue("1、填表日期为当日处理日期T+1日。");
                    Row row3 = sheetAt.createRow((short) (sheetAt.getLastRowNum() + 1));
                    row3.createCell((short) 1).setCellValue("2、支付机构侧日结数据、备付金银行流水均为T日的数据。");
                    Row row4 = sheetAt.createRow((short) (sheetAt.getLastRowNum() + 1));
                    row4.createCell((short) 1).setCellValue("3、此表平衡关系：1=2+3（1-2）=4（1+2-3）；");
                    Row row5 = sheetAt.createRow((short) (sheetAt.getLastRowNum() + 1));
                    row5.createCell((short) 1).setCellValue("4、此表与机构、商户侧日结平衡表对应关系：1=1；2+3（1-2）=2；4=3");
                    //循环处理需要合并的行
                    int i = 6;
                    for (DayAccountReportData dayAccountReportData : dayAccountReportDataList){
                        if(dayAccountReportData.getDayBalanceReportDataList().size() > 1) {
                            sheetAt.addMergedRegion(new CellRangeAddress(i, i + dayAccountReportData.getDayBalanceReportDataList().size() - 1, 4, 4));
                            sheetAt.addMergedRegion(new CellRangeAddress(i, i + dayAccountReportData.getDayBalanceReportDataList().size() - 1, 5, 5));
                            sheetAt.addMergedRegion(new CellRangeAddress(i, i + dayAccountReportData.getDayBalanceReportDataList().size() - 1, 6, 6));
                            sheetAt.addMergedRegion(new CellRangeAddress(i, i + dayAccountReportData.getDayBalanceReportDataList().size() - 1, 9, 9));
                            sheetAt.addMergedRegion(new CellRangeAddress(i, i + dayAccountReportData.getDayBalanceReportDataList().size() - 1, 11, 11));
                        }
                        i+=dayAccountReportData.getDayBalanceReportDataList().size();
                    }
                }
            } catch (Exception e) {
                log.error("系统异常", e);
                wb = new XSSFWorkbook(inputStream);
            }
        } else {
            throw new BusiException("不支持的EXCEL类型");
        }

        POIExcelWriterProcessor processor = new POIExcelWriterProcessor(outputStream, wb);

        TemplateExcelWriterCallBack callBack = new TemplateExcelWriterCallBack(config);

        callBack.getConfig().setSheetName(wb.getSheetName(0));
        callBack.render(processor);
        outputStream.flush();

    }

    public static void writeTitle(InputStream inputStream, OutputStream outputStream, ExcelWriterConfig config, String time) throws IOException, InvalidFormatException {

        if (!inputStream.markSupported()) {
            inputStream = new PushbackInputStream(inputStream, 8);
        }

        Workbook wb;
        if (POIFSFileSystem.hasPOIFSHeader(inputStream)) {
            try {
                wb = new HSSFWorkbook(inputStream);
                CellStyle style = wb.createCellStyle();
                Sheet sheetAt = wb.getSheetAt(0);
                Row row = sheetAt.getRow(1);
                Cell cell = row.getCell(0);
                cell.setCellStyle(style);
                cell.setCellValue("日期范围：" + time);
            } catch (Exception e) {
                log.error("系统异常", e);
                wb = new XSSFWorkbook(inputStream);
            }
        } else if (POIXMLDocument.hasOOXMLHeader(inputStream)) {
            try {
                wb = new XSSFWorkbook(inputStream);
                CellStyle style = wb.createCellStyle();
                style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//中間
                Sheet sheetAt = wb.getSheetAt(0);
                Row row = sheetAt.getRow(2);
                Cell cell = row.getCell(0);
                cell.setCellStyle(style);
                cell.setCellValue(time + "编制");
            } catch (Exception e) {
                log.error("系统异常", e);
                wb = new XSSFWorkbook(inputStream);
            }
        } else {
            throw new BusiException("不支持的EXCEL类型");
        }

        POIExcelWriterProcessor processor = new POIExcelWriterProcessor(outputStream, wb);

        TemplateExcelWriterCallBack callBack = new TemplateExcelWriterCallBack(config);

        callBack.getConfig().setSheetName(wb.getSheetName(0));
        callBack.render(processor);
        outputStream.flush();

    }
}
